﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using trangbaocao.Models;
using System.Data;
using System.Data.OracleClient;
using Microsoft.Reporting.WebForms;
namespace trangbaocao.Reports.Fuel.FuelGenerator
{
    public partial class ReportFuelGenerator : System.Web.UI.Page
    {
        StationsModel StationsModel = new StationsModel();
        Main_StationsModel Main_StationsModel = new Main_StationsModel();
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                if (Session != null && Convert.ToInt32(Session["quyen_id"]) > 2)
                {
                    Radio1.Visible = false;
                }
                List<Main_StationsModel> main_StationsModel = Main_StationsModel.getMain_Stations();
                List<StationsModel> stationModel = StationsModel.getStationsByMain_Stations(Convert.ToInt32(main_StationsModel[0].id));
                cbtrungtam.DataSource = main_StationsModel;
                cbtrungtam.DataTextField = "name";
                cbtrungtam.DataValueField = "id";
                cbtrungtam.DataBind();

                //cbtram.DataSource = stationModel;
                //cbtram.DataTextField = "name";
                //cbtram.DataValueField = "id";
                //cbtram.DataBind();

                cbnam.DataSource = getYear();
                cbnam.DataTextField = "nam";
                cbnam.DataValueField = "nam";
                cbnam.DataBind();

                for (int i = 1; i <= 12; i++)
                    cbthang.Items.Add(i.ToString());
                cbthang.Items.FindByValue(DateTime.Now.Month.ToString()).Selected = true;


            }
        }

        protected void Radio1_CheckedChanged(object sender, EventArgs e)
        {
            //if (Radio1.Checked)
            //{
            //    cbtrungtam.Enabled = false;
            //    cbtram.Enabled = false;
            //}
            //if (Radio2.Checked)
            //{
            //    cbtrungtam.Enabled = true;
            //    cbtram.Enabled = true;
            //}
        }

        protected void cbtrungtam_SelectedIndexChanged(object sender, EventArgs e)
        {
            //List<StationsModel> stationModel = StationsModel.getStationsByMain_Stations(Convert.ToInt32(cbtrungtam.SelectedValue));
            //cbtram.DataSource = stationModel;
            //cbtram.DataTextField = "name";
            //cbtram.DataValueField = "id";
            //cbtram.DataBind();
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            ReportViewer1.Reset();
            Main_Sort_DeviceModel Main_Sort_DeviceModel = new Main_Sort_DeviceModel();
            List<Main_Sort_DeviceModel> main_Sort_DeviceModel = Main_Sort_DeviceModel.getChungloaithietbi();
            ReportViewer1.LocalReport.ReportPath = "Reports/Fuel/FuelGenerator/ReportFuelGenerator.rdlc";
            ReportViewer1.LocalReport.DataSources.Clear();
            ReportViewer1.LocalReport.DataSources.Add(new ReportDataSource("fuelgenerator_dataset", getAllFuelStations().Tables[0]));

            ReportViewer1.DataBind();
            ReportViewer1.LocalReport.Refresh();
        }
        public DataSet getYear()
        {
            string sql = "SELECT distinct nam  from fuel_input order by nam desc";
            OracleConnection conn = General.ConnectDataBase();
            OracleCommand omd = new OracleCommand(sql, conn);
            OracleDataAdapter oda = new OracleDataAdapter(omd);
            DataSet ds = new DataSet();
            try
            {
                conn.Open();
            }
            catch
            {
                System.Web.HttpContext.Current.Response.Write("<SCRIPT LANGUAGE='JavaScript'>alert('Lỗi kết nối với cơ sở dữ liệu!')</SCRIPT>");
            }
            oda.Fill(ds, "0");
            conn.Close();
            return ds;
        }
        public DataSet getAllFuelStations()
        {
            string sql = "";
            string nam = cbnam.SelectedValue;
            string thang = cbthang.SelectedValue;
            string trung_tam_id = cbtrungtam.SelectedValue;
            //string tram_id = cbtram.SelectedValue;

            //string sql_tram = "";
            //if (radio_tram.Checked)
            //    sql_tram = " and stations_id='" + tram_id + "'";
            if (Radio2.Checked)
            {
                //sql += "select a.ma_he_thong,a.ten_thiet_bi,a.ten_tram,a.dia_chi,a.ten_trung_tam,a.ton_dau_thang,a.nhap_trong_thang,a.muc_dung,a.tien_dien_thang,a.thoi_gian_chay,a.luy_ke_tien,a.luy_ke_nhien_lieu,a.luy_ke_thoi_gian,a.ngay_nhap,a.ghi_chu,a.nam,a.thang from";
                //sql += " (select a.ma_he_thong,a.name ten_thiet_bi,c.name ten_tram,c.dia_chi,b.name ten_trung_tam,nvl(a.ton_dau_thang,0) ton_dau_thang,nvl(a.nhap_trong_thang,0) nhap_trong_thang,nvl(muc_dung,0) muc_dung,nvl(don_gia,0) don_gia,nvl(muc_dung,0)*nvl(don_gia,0)";
                //sql += " tien_dien_thang,nvl(thoi_gian_chay,0) thoi_gian_chay,nvl(luy_ke_tien,0) luy_ke_tien,nvl(a.luy_ke_nhien_lieu,0) luy_ke_nhien_lieu,";
                //sql += " nvl(a.luy_ke_thoi_gian,0) luy_ke_thoi_gian,to_char(a.ngay_nhap,'dd-MM-yyyy') ngay_nhap,a.ghi_chu,b.id main_stations_id,c.id stations_id,'" + nam + "' nam,nvl(a.thang,'" + thang + "') thang from";
                //sql += " (select b.id device_stations_id,b.name,b.ma_he_thong,b.main_stations_id,b.stations_id,";
                //sql += " a.muc_dung,a.don_gia ,a.luy_ke_tien,a.ngay_nhap,a.ghi_chu,a.ton_dau_thang,a.nhap_trong_thang,a.luy_ke_nhien_lieu,a.luy_ke_thoi_gian,a.thoi_gian_chay,a.nam,a.thang from";
                //sql += " (select b.id,b.main_stations_id,b.device_stations_id,a.stations_id,b.muc_dung,b.don_gia,b.luy_ke_tien,b.ngay_nhap,";
                //sql += " b.ghi_chu,b.ton_dau_thang,b.nhap_trong_thang,b.luy_ke_nhien_lieu,b.luy_ke_thoi_gian,b.thoi_gian_chay,b.nam,b.thang from";
                //sql += " (select max(id) id,stations_id from fuel where device_stations_id is not null and nam='" + nam + "' and thang<='" + thang + "' group by stations_id)A,fuel b";
                //sql += " where a.id=b.id and b.nam='" + nam + "') A, device_stations b where  a.device_stations_id(+)=b.id and b.main_sort_device_id=9) A,main_stations b,stations c";
                //sql += " where  a.main_stations_id=b.id and a.stations_id=c.id order by b.name,c.name,a.name)a where a.nam='" + nam + "' and thang<='" + thang + "' and main_stations_id='" + trung_tam_id + "'" + sql_tram;
                sql += "select a.ten_trung_tam,a.nhap_trong_thang,a.don_gia,a.tong_tien,b.luy_ke_tien,b.luy_ke_nhien_lieu,a.ngay_nhap,a.ghi_chu from";
                sql += " (select c.name ten_trung_tam,a.nhap_trong_thang,a.don_gia,nvl(a.tong_tien,0) tong_tien,a.ngay_nhap,a.ghi_chu,c.id main_stations_id,nvl(a.id,0) id,nvl(a.thang,0) thang,nvl(a.nam,0) nam  from";
                sql += " (select a.main_stations_id,b.nhap_trong_thang,b.don_gia,b.ngay_nhap,b.ghi_chu,b.tong_tien,b.id,b.thang,b.nam from(";
                sql += " Select B.Id, A.main_stations_id, A.thang From ( select main_stations_id,max(thang) thang from fuel_input Where fuel_type_id>1 and nam=" + nam + " and thang<=" + thang + " group by main_stations_id) A, fuel_input B";
                sql += " where fuel_type_id>1  And  A.thang=B.thang And A.main_stations_id=B.main_stations_id  and B.nam=" + nam + ")A,fuel_input b where a.id=b.id ) A,main_stations C where a.main_stations_id(+)=c.id and c.id="+trung_tam_id+" order by c.name)a,";
                sql += " (select main_stations_id,sum(tong_tien) luy_ke_tien,sum(nhap_trong_thang) luy_ke_nhien_lieu from fuel_input where nam=" + nam + " and thang<=" + thang + " and fuel_type_id>1 group by main_stations_id) b where a.main_stations_id=b.main_stations_id(+)";
            }
            if (Radio1.Checked)
            {
                sql += "select a.ten_trung_tam,a.nhap_trong_thang,a.don_gia,a.tong_tien,b.luy_ke_tien,b.luy_ke_nhien_lieu,a.ngay_nhap,a.ghi_chu from";
                sql += " (select c.name ten_trung_tam,a.nhap_trong_thang,a.don_gia,nvl(a.tong_tien,0) tong_tien,a.ngay_nhap,a.ghi_chu,c.id main_stations_id,nvl(a.id,0) id,nvl(a.thang,0) thang,nvl(a.nam,0) nam  from";
                sql += " (select a.main_stations_id,b.nhap_trong_thang,b.don_gia,b.ngay_nhap,b.ghi_chu,b.tong_tien,b.id,b.thang,b.nam from(";
                sql += " Select B.Id, A.main_stations_id, A.thang From ( select main_stations_id,max(thang) thang from fuel_input Where fuel_type_id>1 and nam=" + nam + " and thang<=" + thang + " group by main_stations_id) A, fuel_input B";
                sql += " where fuel_type_id>1  And  A.thang=B.thang And A.main_stations_id=B.main_stations_id  and B.nam=" + nam + ")A,fuel_input b where a.id=b.id ) A,main_stations C where a.main_stations_id(+)=c.id order by c.name)a,";
                sql += " (select main_stations_id,sum(tong_tien) luy_ke_tien,sum(nhap_trong_thang) luy_ke_nhien_lieu from fuel_input where nam=" + nam + " and thang<=" + thang + " and fuel_type_id>1 group by main_stations_id) b where a.main_stations_id=b.main_stations_id(+)";
            }
            
                 
            OracleConnection conn = General.ConnectDataBase();
            OracleCommand omd = new OracleCommand(sql, conn);
            OracleDataAdapter oda = new OracleDataAdapter(omd);
            DataSet ds = new DataSet();
            try
            {
                conn.Open();
            }
            catch
            {
                System.Web.HttpContext.Current.Response.Write("<SCRIPT LANGUAGE='JavaScript'>alert('Lỗi kết nối với cơ sở dữ liệu!')</SCRIPT>");
            }
            oda.Fill(ds, "0");
            conn.Close();
            return ds;
        }

        protected void cbtram_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        //protected void radio_tram_group_CheckedChanged(object sender, EventArgs e)
        //{
        //    if (radio_tram_group.Checked)
        //    {
        //        cbtram.Enabled = false;
        //    }
        //    if (radio_tram.Checked)
        //    {
        //        cbtram.Enabled = true;
        //    }
        //}
    }
}